/**
 * Created by jackliu on 15/12/16.
 */

var common = require('../../common');
var pool = require('../index');
var mysql = require('mysql');
var ItbBaseModel = require('./ItbBaseModel');
var ItbPageModel = require('./ItbPageModel');


// Schema 结构
var ItbDocumentModel = function(obj){
    ItbBaseModel.call(this,obj);

  // 以下为表定义数据，需要的字段就填入
  //`doc_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '模版／文档ID',
  //`base_doc_id` bigint(20) unsigned DEFAULT NULL COMMENT '基于的模板ID（仅针对用户）',
  //`doc_name` varchar(100) DEFAULT NULL COMMENT '文档名称',
  //`user_id` bigint(20) NOT NULL COMMENT '用户ID',
  //`user_type` int(1) NOT NULL DEFAULT '0' COMMENT '0后台; 1用户',
  //`publish` int(1) NOT NULL DEFAULT '0' COMMENT '0制作中; 1已完成',
  //`data` mediumtext COMMENT '模版内容（除page以外的信息）',
  //`ver` int(10) NOT NULL DEFAULT '1' COMMENT '版本号',
  //`create_time` datetime NOT NULL,
  //`update_time` datetime NOT NULL,
  //`update_by` varchar(50) NOT NULL DEFAULT '',
  //`del_flg` int(1) NOT NULL DEFAULT '0',


};
ItbDocumentModel.prototype = new ItbBaseModel();

// 取得表名
ItbDocumentModel.prototype.getTableName = function(){
    return 'itb_document';
}
// 取得查询语句中where 后面匹配主键的条件（需要各派生类进行重载）
ItbDocumentModel.prototype.getWhereConditionOfKeyEqual = function(){
    return 'doc_id='+this.doc_id;
}

//------------------------------------------------------------------
/* 根据文档id获得文档json字符串，包括页数据在内.
 * 参数:callback:回调，参数
 *          error:错误，null表示无错误
 *          docData:文档的json字符串
 *          docVer:版本号
 *          pageModels:页面数据,ItbPageModel的数组
 * */
ItbDocumentModel.prototype.getDocJsonById=function(callback){

    // 查询文档数据
    this.findFromDB(function (error, items) {
        items = items||[];

        if(error){
            callback(error, null);
            return;
        }

        if(items.length==0){
            callback(null, null);
            return;
        }else{
            var docData = items[0].data;
            var docVer = items[0].ver;
            var docId = items[0].doc_id;

            var entity2 = new ItbPageModel({
                doc_id:docId,
                del_flg:0,
            });
            entity2.findFromDB(function (error2, items2) {
                items2 = items2||[];


                // 查询页面数据

                var pages = '"pages":[';
                for(var i=0;i<items2.length;i++){
                    if(i>0){
                        pages +=',';
                    }
                    pages += items2[i].data;

                }
                pages+=']'

                // 拼接  文档数据和页面数据
                docData = docData.replace('"pages":[]', pages);
                // 替换掉转义符
                docData = docData.replace(/\n/g,'\\n');

                callback(null,docData,docVer,items2);

            });
        }
    });
}

//---------------------------------end-------------------------------

//------------------------------------------------------------------
/* 根据文档id获得文档的可替换数组的json字符串，包括页数据在内.
 * 参数:callback:回调，参数
 *          error:错误，null表示无错误
 *          replacesData:文档的可替换数组的json字符串，数组的json字符串结构
 *          docVer:版本号
 *          pageIds:page的id数组
 * */
ItbDocumentModel.prototype.getDocReplacesJsonById=function(callback){

    // 查询文档数据
    this.findFromDB(function (error, items) {
        items = items||[];

        if(error){
            callback(error, null);
            return;
        }

        if(items.length==0){
            callback("not find", null);
            return;
        }else{
            var docVer = items[0].ver;
            var docId = items[0].doc_id;

            var entity2 = new ItbPageModel({
                doc_id:docId,
                del_flg:0,
            });
            entity2.findFromDB(function (error2, items2) {
                items2 = items2||[];


                // 查询页面数据
                var pageIds=[];
                var replaces = '[';
                for(var i=0;i<items2.length;i++){
                    if(i>0){
                        replaces +=',';
                    }
                    replaces += items2[i].replaces;

                    pageIds.push(items2[i].page_id)
                }
                replaces+=']'

                // 替换掉转义符
                replaces = replaces.replace(/\n/g,'\\n');

                callback(null,replaces,docVer,pageIds);

            });
        }
    });
}

//---------------------------------end-------------------------------

//------------------------------------------------------------------
/* 根据文档id删除对应文档及page信息.
 * 参数:callback:回调，参数
 *          error:错误，null表示无错误
 *          docData:文档的json字符串
 *          docVer:版本号
 *          pages:页面数据
 * */
ItbDocumentModel.prototype.deleteDocAndPagesLogic=function(callback){
    var THIS = this;
    var tableName = this.getTableName();

    // delete document
    pool.pool.getConnection(function(err, connection) {
        var sql = 'UPDATE '+tableName+' SET del_flg=1, update_time=sysdate(), update_by=?' +
            ' WHERE doc_id=?';
        var sqlValue = [THIS.update_by, THIS.doc_id];
        var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            if(error) {
                callback(error);
            } else {
                // delete pages
                var pageEntity = new ItbPageModel({
                    doc_id: THIS.doc_id,
                    update_by: THIS.update_by
                });
                pageEntity.deletePagesByDocIdLogic(function(error){
                    if (error) {
                        callback(error)
                    } else {
                        callback(null);
                    }
                });
            }
        });
    });

}
//---------------------------------end-------------------------------

//------------------------------------------------------------------
/* 根据[文档分类]查找该分类下所有文档(模版)信息.
 * 注: 后台模版有分类，用户文档无分类
 * 参数:callback:回调，参数
 *
 * 参数:category:0最新/1行业/2用途
 * 参数:code:(即type_id,等于0时表示不限)
 *          在category为0时,表示最新
 *          在category为1时,表示行业
 *          在category为2时,表示用途
 * 参数: skipCnt,pageSize 分页用
 * */
ItbDocumentModel.prototype.getDocAndPagesInfoByType=function(callback,category,code,skipCnt,pageSize){
    var THIS = this;
    var tableName = this.getTableName();

    // 从mysql中查询
    pool.pool.getConnection(function(err, connection) {
        var sql =
            ' SELECT doc.doc_id, doc.doc_name, pg.thumb_s, pc.page_count, '+
            ' DATE_FORMAT(doc.create_time, "%Y/%m/%d %H:%i:%S") AS create_time, '+
            ' tp.type_name '+

            ' FROM itb_document doc, '+
            ' itb_page pg, '+

            ' (SELECT t1.doc_id, GROUP_CONCAT(t2.type_name) AS type_name '+
            ' FROM itb_doc_type_r t1, '+
            ' itb_doc_type t2 '+
            ' WHERE t1.type_id = t2.type_id ';
        if (0 != category && 0 != code) {
            sql +=
                ' AND t2.category = '+ category +
                ' AND t2.type_id = '+ code ;
        }
        sql +=
            ' AND t1.del_flg = 0 '+
            ' AND t2.del_flg = 0 '+
            ' GROUP BY t1.doc_id) tp, '+

            ' (SELECT t.doc_id, count(1) AS page_count '+
            ' FROM itb_page t '+
            ' WHERE t.del_flg = 0 '+
            ' GROUP BY t.doc_id) pc '+

            ' WHERE doc.doc_id = tp.doc_id '+
            ' AND doc.doc_id = pg.doc_id '+
            ' AND pg.page_index = 0 '+
            ' AND doc.doc_id = pc.doc_id '+
            ' AND doc.publish = 1 '+
            ' AND doc.del_flg = 0 '+
            ' AND pg.del_flg = 0 '+
            ' ORDER BY doc.order, doc.update_time DESC ';

        if(typeof skipCnt!='undefined' && skipCnt != null && typeof pageSize!='undefined' && pageSize != null){
            sql += " limit " + Number(skipCnt) + "," + Number(pageSize);
        }

        //var sqlValue = [category, code];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);

        // Use the connection
        connection.query(sql, function(error, items) {
            // And done with the connection.
            connection.release();

            // 回调
            callback(error, items);
        });

    });

}
ItbDocumentModel.prototype.getDocAndPagesCountInfoByType2=function(callback,category,code){
    var THIS = this;
    var tableName = this.getTableName();

    // 从mysql中查询
    pool.pool.getConnection(function(err, connection) {
        var sql =
            ' SELECT count(*) as count'+

            ' FROM itb_template doc, '+
            ' itb_tmpl_page pg, '+

            ' (SELECT t1.doc_id, GROUP_CONCAT(t2.type_name) AS type_name '+
            ' FROM itb_doc_type_r t1, '+
            ' itb_doc_type t2 '+
            ' WHERE t1.type_id = t2.type_id ';
        if (0 != category && 0 != code) {
            sql +=
                ' AND t2.category = '+ category +
                ' AND t2.type_id = '+ code ;
        }
        sql +=
            ' AND t1.del_flg = 0 '+
            ' AND t2.del_flg = 0 '+
            ' GROUP BY t1.doc_id) tp, '+

            ' (SELECT t.tmpl_id, count(1) AS page_count '+
            ' FROM itb_tmpl_page t '+
            ' WHERE t.del_flg = 0 '+
            ' GROUP BY t.tmpl_id) pc '+

            ' WHERE doc.tmpl_id = tp.doc_id '+
            ' AND doc.tmpl_id = pg.tmpl_id '+
            ' AND pg.page_index = 0 '+
            ' AND doc.tmpl_id = pc.tmpl_id '+
            ' AND doc.publish = 1 '+
            ' AND doc.del_flg = 0 '+
            ' AND pg.del_flg = 0 '+
            ' ORDER BY doc.order, doc.update_time DESC ';



        //var sqlValue = [category, code];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);

        // Use the connection
        connection.query(sql, function(error, items) {
            // And done with the connection.
            connection.release();

            // 回调
            if(items && items.length>0){
                callback(error, items[0].count);
            }else{
                callback(error, 0);
            }

        });

    });
}
ItbDocumentModel.prototype.getDocAndPagesInfoByType2=function(callback,category,code,skipCnt,pageSize){
    var THIS = this;
    var tableName = this.getTableName();

    // 从mysql中查询
    pool.pool.getConnection(function(err, connection) {
        var sql =
            ' SELECT doc.tmpl_id as doc_id, doc.tmpl_name as doc_name, pg.thumb_s, pc.page_count, '+
            ' DATE_FORMAT(doc.create_time, "%Y/%m/%d %H:%i:%S") AS create_time, '+
            ' tp.type_name '+

            ' FROM itb_template doc, '+
            ' itb_tmpl_page pg, '+

            ' (SELECT t1.doc_id, GROUP_CONCAT(t2.type_name) AS type_name '+
            ' FROM itb_doc_type_r t1, '+
            ' itb_doc_type t2 '+
            ' WHERE t1.type_id = t2.type_id ';
        if (0 != category && 0 != code) {
            sql +=
                ' AND t2.category = '+ category +
                ' AND t2.type_id = '+ code ;
        }
        sql +=
            ' AND t1.del_flg = 0 '+
            ' AND t2.del_flg = 0 '+
            ' GROUP BY t1.doc_id) tp, '+

            ' (SELECT t.tmpl_id, count(1) AS page_count '+
            ' FROM itb_tmpl_page t '+
            ' WHERE t.del_flg = 0 '+
            ' GROUP BY t.tmpl_id) pc '+

            ' WHERE doc.tmpl_id = tp.doc_id '+
            ' AND doc.tmpl_id = pg.tmpl_id '+
            ' AND pg.page_index = 0 '+
            ' AND doc.tmpl_id = pc.tmpl_id '+
            ' AND doc.publish = 1 '+
            ' AND doc.del_flg = 0 '+
            ' AND pg.del_flg = 0 '+
            ' ORDER BY doc.order, doc.update_time DESC ';

        if(typeof skipCnt!='undefined' && skipCnt != null && typeof pageSize!='undefined' && pageSize != null){
            sql += " limit " + Number(skipCnt) + "," + Number(pageSize);
        }

        //var sqlValue = [category, code];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);

        // Use the connection
        connection.query(sql, function(error, items) {
            // And done with the connection.
            connection.release();

            // 回调
            callback(error, items);
        });

    });

}
//---------------------------------end-------------------------------

//------------------------------------------------------------------
/* 根据文档id(多个时逗号连接)获取pages信息
 * 参数:callback:回调，参数
 *
 * 参数:doc_id: 文档id
 * */
ItbDocumentModel.prototype.getPagesInfoByDocId = function(callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql = 'SELECT p.*, t.doc_name, t.base_doc_id, t.track ' +
            'FROM itb_document t, itb_page p ' +
            'WHERE t.del_flg = 0 AND p.del_flg = 0 ' +
            'AND t.doc_id = ? AND t.doc_id = p.doc_id ' +
            'ORDER BY p.page_index ';
        var sqlValue = [THIS.doc_id];
        sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            callback(error, result);
        });
    });
}
//---------------------------------end-------------------------------

//-------------------------------start-----------------------------------
/* 查询出“用户文档页”对应的“模版文档数据”
 * 参数:user_doc_id:用户doc id
 * 参数:callback:回调，参数
 *          error:错误,null表示无错误
 *          documentModel:模版文档数据,ItbDocumentModel的数组
 * */
ItbDocumentModel.prototype.getTemplateDocumentByUserDocId=function(user_doc_id,callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbPageModel.getTemplateDocumentByUserDocId Err:'+err);
        }
        // Use the connection
        var sql =
            ' select * from itb_template d1 '+
            ' where d1.tmpl_id=( '+
            ' select d.base_doc_id from itb_document d '+
            ' where d.doc_id='+user_doc_id+
            ' and d.user_type=1 '+
            ' and d.del_flg=0) ';

        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            callback(error, result);
        });
    });
}
//---------------------------------end-------------------------------

//------------------------------------------------------------------
/* 获取最新娱乐用文档.
 * 参数:callback:回调，参数
 * */
ItbDocumentModel.prototype.getLatestEntDoc=function(callback){
    var THIS = this;
    var tableName = this.getTableName();

    // 从mysql中查询
    pool.pool.getConnection(function(err, connection) {
        var sql =
            ' SELECT t.tmpl_id '+
            ' FROM itb_template t, '+
            '      itb_doc_type_r t1, '+
            '      itb_doc_type t2 '+
            ' WHERE t2.category = 2 '+// 用途
            '   AND t2.type_index = 7 '+// 节日娱乐
            '   AND t1.type_id = t2.type_id '+
            '   AND t.tmpl_id = t1.doc_id '+
            '   AND t.user_type = 0 '+// 模版数据
            '   AND t.del_flg = 0 '+
            '   AND t1.del_flg = 0 '+
            '   AND t2.del_flg = 0 '+
            ' ORDER BY t.update_time DESC '+
            ' LIMIT 0,1 ';

        console.log(sql);

        // Use the connection
        connection.query(sql, function(error, items) {
            // And done with the connection.
            connection.release();

            // 回调
            callback(error, items);
        });

    });

}
//---------------------------------end-------------------------------

//------------------------------------------------------------------
/* 设置文档是否可以追踪
 * 参数:ids:逗号连接文档id
 * 参数:callback:回调
 * */
ItbDocumentModel.prototype.setDocTrack=function(callback){
    var THIS = this;
    var tableName = this.getTableName();

    // update document
    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbDocumentModel.setDocTrack Err:'+err);
        }

        var sql = ' UPDATE itb_document ' +
            ' SET update_time=sysdate(), track='+ THIS.track +
            ' , update_by=?'+
            ' WHERE doc_id IN (' + THIS.ids + ') and del_flg=0 ';
        var sqlValue = [THIS.update_by];
        var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            callback(error, result);
        });
    });
}
//---------------------------------end-------------------------------

//------------------------------------------------------------------
/* 获取文档指定天数总的追踪信息.
 * 参数:ids
 * 参数:type (等于0时取所有类型)
 * 参数:period (最近天数)
 * 参数:callback:回调
 * */
ItbDocumentModel.prototype.getTrackInfoByDocId=function(callback){
    var THIS = this;
    var tableName = this.getTableName();

    // 从mysql中查询
    pool.pool.getConnection(function(err, connection) {
        var sql = ' SELECT d.doc_id, d.track, A.read_cnt, A.duration, B.relay_cnt ' +
                ' FROM itb_document d ';

        // 阅览数
        if (0 == THIS.type || 1 == THIS.type) {
            sql += ' LEFT JOIN ('+
                ' SELECT t.doc_id, count(*) as read_cnt, ' +
                ' ROUND(SUM(CASE WHEN t.last_time - t.start_time = 0 THEN 5 ELSE t.last_time - t.start_time END)/1000) AS duration ' +
                ' FROM itb_browse_info t '+
                ' WHERE t.doc_id IN ( ' + THIS.ids + ' ) AND t.del_flg = 0 ';
            if (THIS.period && THIS.period>0) {
                sql += ' AND to_days(now()) - to_days(t.create_time) < ' + THIS.period;
            }
            sql += ' GROUP BY t.doc_id ) A ON d.doc_id = A.doc_id ';
        }

        // 转发数
        if (0 == THIS.type || 2 == THIS.type) {
            sql += ' LEFT JOIN ( '+
                ' SELECT t.doc_id, count(*) as relay_cnt FROM itb_relay_info t '+
                ' WHERE t.doc_id IN ( ' + THIS.ids + ') AND t.del_flg = 0 ';
            if (THIS.period && THIS.period>0) {
                sql += ' AND to_days(now()) - to_days(t.create_time) < ' + THIS.period;
            }
            sql += ' GROUP BY t.doc_id ) B ON d.doc_id = B.doc_id '
        }

        sql += ' WHERE d.doc_id IN ( ' + THIS.ids + ' ) AND d.del_flg = 0 ORDER BY d.doc_id';

        // 收藏数
        //if (0 == THIS.type || 3 == THIS.type) {
        //    sql += ' UNION '+
        //        ' SELECT 3 as type, count(*) as cnt FROM itb_collect t '+
        //        ' WHERE t.del_flg = 0 AND t.doc_id = ' + THIS.doc_id;
        //    if (THIS.period && THIS.period>0) {
        //        sql += ' AND to_days(now()) - to_days(t.create_time) < ' + THIS.period;
        //    }
        //}

        //var sqlValue = [THIS.doc_id,THIS.doc_id,THIS.doc_id];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);

        // Use the connection
        connection.query(sql, function(error, items) {
            // And done with the connection.
            connection.release();

            // 回调
            callback(error, items);
        });

    });
}
//---------------------------------end-------------------------------

//------------------------------------------------------------------
/* 获取文档指定天数每天的追踪信息.
 * 参数:doc_id
 * 参数:type (等于0时取所有类型)
 * 参数:period (最近天数)
 * 参数:callback:回调
 * */
ItbDocumentModel.prototype.getDayTrackInfoByDocId=function(callback){
    var THIS = this;
    var tableName = this.getTableName();

    // 从mysql中查询
    pool.pool.getConnection(function(err, connection) {
        var sql = 'SELECT null as day, 0 as type, 0 as cnt FROM dual WHERE 0=1 ';

        // 阅览数
        if (0 == THIS.type || 1 == THIS.type) {
            sql += ' UNION '+
                " SELECT DATE_FORMAT(date(t.create_time), '%Y%m%d') as day, 1 as type, count(*) as cnt FROM itb_browse_info t "+
                ' WHERE t.del_flg = 0 AND t.doc_id = ' + THIS.doc_id;
            if (THIS.period && THIS.period>0) {
                sql += ' AND to_days(now()) - to_days(t.create_time) < ' + THIS.period;
            }
            sql += ' GROUP BY date(t.create_time) ';
        }

        // 转发数
        if (0 == THIS.type || 2 == THIS.type) {
            sql += ' UNION '+
                " SELECT DATE_FORMAT(date(t.create_time), '%Y%m%d') as day, 2 as type, count(*) as cnt FROM itb_relay_info t "+
                ' WHERE t.del_flg = 0 AND t.doc_id = ' + THIS.doc_id;
            if (THIS.period && THIS.period>0) {
                sql += ' AND to_days(now()) - to_days(t.create_time) < ' + THIS.period;
            }
            sql += ' GROUP BY date(t.create_time) ';
        }

        // 收藏数
        //if (0 == THIS.type || 3 == THIS.type) {
        //    sql += ' UNION '+
        //        " SELECT DATE_FORMAT(date(t.create_time), '%Y%m%d') as day, 3 as type, count(*) as cnt FROM itb_collect t "+
        //        ' WHERE t.del_flg = 0 AND t.doc_id = ' + THIS.doc_id;
        //    if (THIS.period && THIS.period>0) {
        //        sql += ' AND to_days(now()) - to_days(t.create_time) < ' + THIS.period;
        //    }
        //    sql += ' GROUP BY date(t.create_time) ';
        //}

        //var sqlValue = [THIS.doc_id,THIS.doc_id,THIS.doc_id];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);

        // Use the connection
        connection.query(sql, function(error, items) {
            // And done with the connection.
            connection.release();

            // 回调
            callback(error, items);
        });

    });
}
//---------------------------------end-------------------------------

//------------------------------------------------------------------
/* 获取微信用户的文档追踪信息.
 * 参数: doc_id
 * 参数: orderBy 排序方式(1阅读时长,2转发次数,3最新)
 * 参数: callback:回调
 * 参数: skipCnt,pageSize 分页用
 * */
ItbDocumentModel.prototype.getWxUserTrackInfo=function(callback,orderBy,skipCnt,pageSize){
    var THIS = this;
    var tableName = this.getTableName();

    // 从mysql中查询
    pool.pool.getConnection(function(err, connection) {
        var sql =
            ' SELECT A.union_id, ' +
            '     A.read_cnt, ' +
            '     A.duration, ' +
            '     IFNULL(B.relay_cnt,0) AS relay_cnt, ' +
            '     A.nickname, ' +
            '     A.headimgurl, ' +
            '     CASE ' +
            ' WHEN A.update_time>=B.update_time ' +
            ' OR B.update_time IS NULL THEN A.update_time ' +
            ' ELSE B.update_time ' +
            ' END AS update_time ' +
            ' FROM ' +
            ' (SELECT t.union_id, ' +
            '     t.read_cnt, ' +
            '     t.duration, ' +
            '     b.nickname, ' +
            '     b.headimgurl, ' +
            '     b.update_time ' +
            ' FROM ' +
            ' (SELECT bi.union_id, ' +
            '     MAX(bi.id) AS maxid, ' +
            '     COUNT(*) AS read_cnt, ' +
            '     ROUND(SUM(CASE WHEN bi.last_time - bi.start_time = 0 THEN 5 ELSE bi.last_time - bi.start_time END)/1000) AS duration ' +
            ' FROM itb_browse_info bi ' +
            ' WHERE bi.doc_id = ' + THIS.doc_id +
            ' AND bi.del_flg = 0 ' +
            ' GROUP BY bi.union_id) t ' +
            ' INNER JOIN itb_browse_info b ON b.id = t.maxid) A ' +
            ' LEFT JOIN ' +
            ' (SELECT t2.union_id, ' +
            '     t2.relay_cnt, ' +
            '     r.update_time ' +
            ' FROM ' +
            ' (SELECT ri.union_id, ' +
                '     MAX(ri.id) AS maxid, ' +
                ' COUNT(*) AS relay_cnt ' +
            ' FROM itb_relay_info ri ' +
            ' WHERE ri.doc_id = ' + THIS.doc_id +
            ' AND ri.del_flg = 0 ' +
            ' GROUP BY ri.union_id) t2 ' +
            ' INNER JOIN itb_relay_info r ON r.id = t2.maxid) B ON A.union_id = B.union_id ';
        if (1 == orderBy) {
            sql += ' ORDER BY duration DESC ';
        } else if (2 == orderBy) {
            sql += ' ORDER BY relay_cnt DESC ';
        } else if (3 == orderBy) {
            sql += ' ORDER BY update_time DESC ';
        }

        if (typeof skipCnt!='undefined' && skipCnt != null && typeof pageSize!='undefined' && pageSize != null){
            sql += " limit " + Number(skipCnt) + "," + Number(pageSize);
        }

        //var sqlValue = [THIS.doc_id,THIS.doc_id,THIS.doc_id];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);

        // Use the connection
        connection.query(sql, function(error, items) {
            // And done with the connection.
            connection.release();

            // 回调
            callback(error, items);
        });

    });
}
//---------------------------------end-------------------------------

//------------------------------------------------------------------
/* 将From-UserId下的文档转移到To-UserId下
 * 参数 user_id_from: 源用户
 * 参数 user_id_to: 目标用户
 * 参数 callback: 回调
 * */
ItbDocumentModel.prototype.moveDocByUser=function(callback){
    var THIS = this;
    var tableName = this.getTableName();

    // delete document
    pool.pool.getConnection(function(err, connection) {
        var sql = ' UPDATE ITB_DOCUMENT SET user_id=?, update_time=sysdate(), update_by=? ' +
            ' WHERE user_id=? AND user_type=1 AND del_flg=0 ';
        var sqlValue = [THIS.user_id_to, THIS.user_id_to, THIS.user_id_from];
        var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            console.log();

            callback(error, result);
        });
    });
}
//---------------------------------end-------------------------------

//------------------------------------------------------------------
/* 获取指定user_id对应的文档数
 * 参数 ids: 逗号连接的user_id
 * 参数 callback: 回调
 * */
ItbDocumentModel.prototype.cntDocByUser=function(callback){
    var THIS = this;
    var tableName = this.getTableName();

    // delete document
    pool.pool.getConnection(function(err, connection) {
        var sql = ' SELECT A.user_id, IFNULL(B.cnt,0) AS cnt FROM ' +
            ' (SELECT user_id FROM itb_user ' +
            ' WHERE user_id IN ( ' + THIS.ids + ' )  AND del_flg=0 ) A' +
            ' LEFT JOIN ' +
            ' (SELECT user_id, count(*) AS cnt FROM itb_document ' +
            ' WHERE user_id IN ( ' + THIS.ids + ' )  AND del_flg=0 ' +
            ' GROUP BY user_id ) B' +
            ' ON A.user_id = B.user_id ORDER BY A.user_id ';

        //var sqlValue = [THIS.user_id_from, THIS.user_id_from, THIS.user_id_to];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, results) {
            // And done with the connection.
            connection.release();
            console.log();

            callback(error, results);
        });
    });
}
//---------------------------------end-------------------------------

//------------------------------------------------------------------
/* 根据用户id获取相关文档总数
 * 参数:callback:回调，参数
 *
 * 参数 user_id: 用户id
 * 参数 orderBy: 1:create_time倒序,0或空:doc_id顺序
 * */
ItbDocumentModel.prototype.getDocCountInfoByUser = function(callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql = ' SELECT count(*) as count' +
            ' FROM itb_document t, itb_page p ' +
            ' LEFT JOIN itb_tmpl_page tmp ' +// 取模版数据
            ' ON tmp.del_flg = 0 AND tmp.page_id = p.base_page_id AND tmp.user_type = 0 ' +
            ' WHERE t.user_id = ? AND t.doc_id = p.doc_id ' +
            ' AND p.page_index = 0 AND t.del_flg = 0 AND p.del_flg = 0 ';


        var sqlValue = [THIS.user_id];
        sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, items) {
            // And done with the connection.
            connection.release();

            console.log();

            if(items && items.length>0){
                callback(error, items[0].count);
            }else{
                callback(error, 0);
            }

        });
    });
}
//---------------------------------end-------------------------------
//------------------------------------------------------------------
/* 根据用户id获取相关文档基本信息
 * 参数:callback:回调，参数
 *
 * 参数 user_id: 用户id
 * 参数 orderBy: 1:create_time倒序,0或空:doc_id顺序
 * */
ItbDocumentModel.prototype.getDocInfoByUser = function(callback,skipCnt,pageSize,orderBy){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql = ' SELECT t.doc_id, t.base_doc_id, t.doc_name, t.user_type, t.track, ' +
            ' DATE_FORMAT(t.create_time, "%Y-%m-%d %T") as create_time, p.thumb_s AS thumb, tmp.thumb_s AS thumb2 ' +
            ' FROM itb_document t, itb_page p ' +
            ' LEFT JOIN itb_tmpl_page tmp ' +// 取模版数据
            ' ON tmp.del_flg = 0 AND tmp.page_id = p.base_page_id AND tmp.user_type = 0 ' +
            ' WHERE t.user_id = ? AND t.doc_id = p.doc_id ' +
            ' AND p.page_index = 0 AND t.del_flg = 0 AND p.del_flg = 0 ';
        if (orderBy == 1) {
            sql += ' ORDER BY t.create_time DESC ';
        } else {
            sql += ' ORDER BY t.doc_id ';
        }
        if (typeof skipCnt!='undefined' && skipCnt != null && typeof pageSize!='undefined' && pageSize != null){
            sql += " limit " + Number(skipCnt) + "," + Number(pageSize);
        }
        var sqlValue = [THIS.user_id];
        sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            callback(error, result);
        });
    });
}
//---------------------------------end-------------------------------

//------------------------------------------------------------------
/* 根据文档id更新audio信息
 */
ItbDocumentModel.prototype.updDocAudio = function(callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        var sql = " UPDATE itb_document SET update_time=sysdate(), audio=?, update_by=? WHERE doc_id=? AND user_id=? ";
        var param = [THIS.audio, THIS.user_id, THIS.doc_id, THIS.user_id];

        var exeSql = mysql.format(sql, param);
        console.log(exeSql);
        connection.query(exeSql, function (error, result) {

            if (error) {
                console.log('ItbDocumentModel.js [updDocAudio] Upd Err: ' + error);
            }

            connection.release();

            callback(error, result);
        });
    });
}
//---------------------------------end-------------------------------

//------------------------------------------------------------------
/* 安全删除用户文档
 * 参数 user_id: 用户id
 * 参数 id: 文档id
 * 参数 callback: 回调
 * */
ItbDocumentModel.prototype.safeRemoveDoc=function(user_id, id, callback){
    var THIS = this;
    var tableName = this.getTableName();

    // delete document
    pool.pool.getConnection(function(err, connection) {
        var sql = ' UPDATE itb_document SET del_flg=1, update_time=sysdate(), update_by=' +user_id+
            ' WHERE user_id=' +user_id+' AND doc_id='+id+' AND del_flg=0 ';
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            console.log();

            if(error){
                return callback(error);
            }
            if(!result){
                return callback(null);
            }
            if(result.affectedRows==0){
                return callback("删除失败");
            }
            return callback(null);

        });
    });
}
//---------------------------------end-------------------------------



// 导出
module.exports = ItbDocumentModel;